import mysql.connector
from mysql.connector import Error

def conn():
    try:
        mydb = mysql.connector.connect(
            host="192.168.0.236",
            user="root",
            passwd="Xzc@12345",
            database="mcdullx_dw"
        )
        return mydb
    except Error as e:
        print(f"Error connecting to MySQL: {e}")
        return None

def findOne(params, table_name):
    mydb = conn()
    if not mydb:
        return None,None
    try:
        mycursor = mydb.cursor()
        # fields = "road_code,date_time, co_pfl, hc_pfl, nox_pfl, pm25_pfl"
        fields = "road_code,date_time, nox_pfl, pm25_pfl"
        ins_sql = f"SELECT {fields} FROM {{}} WHERE station_code = %s AND date_time BETWEEN %s AND %s AND road_name = %s ORDER BY date_time ASC"
        sql = ins_sql.format(table_name)
        # print(f"Executing SQL: {sql} with params: {params}")
        mycursor.execute(sql, params)
        rs = mycursor.fetchall()
        result_dict,road_code = map_to_time_dict(rs)
        return result_dict,road_code
    except Error as e:
        print(f"Error executing query: {e}")
        return None,None
    finally:
        if mydb.is_connected():
            mycursor.close()
            mydb.close()

def map_to_time_dict(rs):
    result_dict = {}
    road_code = ""
    for row in rs:
        try:
            road_code = row[0]
            formatted_date_time = row[1].strftime('%Y-%m-%d %H:%M:%S')
            values = row[2:]
            result_dict[formatted_date_time] = {
                'nox': float(values[0]),
                'pm25': float(values[1])
            }
        except (ValueError, IndexError) as e:
            print(f"Error processing row: {e}")
    return result_dict,road_code